﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using BCEntity.MySql.FreightConfigurationExtention;
using BCCommon;
using BCCommon.TMS.Order;
using BCCommon.TMS.FreightEnums;

namespace BCData.TMS.FreightConfigurationExtention
{
    public class FreightConfigurationExtentionData : IFreightConfigurationExtentionData
    {
        private const string QUERY = @"
                        SELECT
	                        A.FreightConfigurationExtentionId,
	                        A.ProvinceCode,
	                        A.CityCode,
	                        A.CountyCode,
	                        A.FirstWeight,
	                        A.MaxWeight,
	                        A.StartPrice,
	                        A.EveryWeightPrice,
	                        A.CreateTime,
	                        A.UpdateTime,
	                        A.Editer,
                            A.SourceType,
                            A.FreightType,
                            A.VehicleTypeId,
                            A.MileagePrice,
	                        CO.CountyName,
	                        P.ProvinceName,
	                        C.CityName,
                            B.VehicleTypeName 
                        FROM
	                        freightconfigurationextention A
	                        LEFT JOIN province P ON A.ProvinceCode = P.ProvinceCode
	                        LEFT JOIN city C ON A.CityCode = C.CityCode
	                        LEFT JOIN county CO ON A.CountyCode = CO.CountyCode 
                            INNER JOIN vehicletype B ON A.VehicleTypeId = B.VehicleTypeId";
        private const string QUERY_DEFAULT = @"SELECT FreightConfigurationExtentionId,UpdateTime,Editer,SourceType,FreightType,SameCityMaxMileage,
                                                    SameCityRatio,SameProvinceMileage,SameProvinceRatio,VehicleTypeId,ProvinceCode,CityCode,CountyCode,
                                                    FirstWeight,MaxWeight,StartPrice,EveryWeightPrice,CreateTime,MileagePrice 
                                                FROM freightconfigurationextention";

        private const string QUERY_JOIN = @"
                        SELECT
	                        A.FreightConfigurationExtentionId,
	                        A.ProvinceCode,
	                        A.CityCode,
	                        A.CountyCode,
	                        A.FirstWeight,
	                        A.MaxWeight,
	                        A.StartPrice,
	                        A.EveryWeightPrice,
	                        A.CreateTime,
	                        A.UpdateTime,
	                        A.Editer,
                            A.SourceType,
                            A.FreightType,
                            A.VehicleTypeId,
                            A.MileagePrice,
	                        CO.CountyName,
	                        P.ProvinceName,
	                        C.CityName,
                            B.VehicleTypeName 
                        FROM
                            AgentAreaRecord F
	                        INNER JOIN FreightConfigurationExtention A ON A.CountyCode = F.CountyCode
	                        LEFT JOIN province P ON A.ProvinceCode = P.ProvinceCode
	                        LEFT JOIN city C ON A.CityCode = C.CityCode
	                        LEFT JOIN county CO ON A.CountyCode = CO.CountyCode 
                            INNER JOIN vehicletype B ON A.VehicleTypeId = B.VehicleTypeId";

        private const string QUERY_COUNT = "SELECT COUNT(1) FROM freightconfigurationextention A";
        private const string QUERY_JOIN_COUNT = @"SELECT COUNT(1) FROM AgentAreaRecord F 
                                                INNER JOIN FreightConfigurationExtention A ON A.CountyCode = F.CountyCode
                                                LEFT JOIN province P ON A.ProvinceCode = P.ProvinceCode
                                                LEFT JOIN city C ON A.CityCode = C.CityCode
                                                LEFT JOIN county CO ON A.CountyCode = CO.CountyCode 
                                                INNER JOIN vehicletype B ON A.VehicleTypeId = B.VehicleTypeId";

        private readonly IDatabaseContext database;

        public FreightConfigurationExtentionData(IDatabaseContext database)
        {
            this.database = database;
        }

        #region 公共方法

        public async Task<FreightConfigurationExtentionEntity> Add(FreightConfigurationExtentionEntity entity)
        {
            return await database.Master.InsertAsync(entity);
        }

        public async Task<bool> CheckFreightConfigurationExtentionExists(string provinceCode, string cityCode, string countyCode,
            decimal firstWeight, decimal maxWeight, decimal startPrice, decimal everyWeightPrice, OrderSourceType sourceType, FreightType freightType,
            int vehicleTypeId)
        {
            string sql = @"SELECT A.FreightConfigurationExtentionId,
	                        A.ProvinceCode,
	                        A.CityCode,
	                        A.CountyCode,
	                        A.FirstWeight,
	                        A.MaxWeight,
	                        A.StartPrice,
	                        A.EveryWeightPrice,
	                        A.CreateTime,
	                        A.UpdateTime,
	                        A.Editer,
                            A.SourceType,
                            A.FreightType,
                            A.VehicleTypeId,
                            A.MileagePrice 
                            FROM FreightConfigurationExtention A
                            WHERE A.ProvinceCode=?ProvinceCode
                            AND A.CityCode=?CityCode
                            AND A.CountyCode=?CountyCode
                            AND A.FirstWeight=?FirstWeight
                            AND A.MaxWeight=?MaxWeight 
                            AND A.EveryWeightPrice = ?EveryWeightPrice 
                            AND A.StartPrice=?StartPrice
                            AND A.SourceType = ?SourceType
                            AND A.FreightType = ?FreightType 
                            AND A.VehicleTypeId = ?VehicleTypeId;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("ProvinceCode", provinceCode, DbType.AnsiString, size: 20);
            parameters.Add("CityCode", cityCode, DbType.AnsiString, size: 20);
            parameters.Add("CountyCode", countyCode, DbType.AnsiString, size: 20);
            parameters.Add("FirstWeight", firstWeight, DbType.Decimal);
            parameters.Add("MaxWeight", maxWeight, DbType.Decimal);
            parameters.Add("EveryWeightPrice", everyWeightPrice, DbType.Decimal);
            parameters.Add("StartPrice", startPrice, DbType.Decimal);
            parameters.Add("?SourceType", sourceType.ToString(), DbType.AnsiString);
            parameters.Add("?FreightType", freightType.ToString(), DbType.AnsiString);
            parameters.Add("?VehicleTypeId", vehicleTypeId, DbType.Int32);
            return await database.Slave.QueryFirstOrDefaultAsync<FreightConfigurationExtentionEntity>(sql, parameters) != null;
        }

        public async Task<int> Delete(int freightConfigurationExtentionId)
        {
            string sql = @"DELETE FROM FreightConfigurationExtention
                            WHERE FreightConfigurationExtentionId=?FreightConfigurationExtentionId;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("FreightConfigurationExtentionId", freightConfigurationExtentionId, DbType.Int32, size: 10);
            return await database.Master.ExecuteAsync(sql, parameters);
        }

        public async Task<FreightConfigurationExtentionEntity> Update(FreightConfigurationExtentionEntity entity)
        {
            //string sql = sqlCommand.GetUpdateSql("FreightConfigurationExtention",
            //    new[] { "ProvinceId", "CommunityId", "CountyId", "StartWeight", "EndWeight", "Stere", "StartPrice", "MileagePrice", "Editer" },
            //    new[] { "FreightConfigurationExtentionId" });
            //DynamicParameters parameters = new DynamicParameters();
            //parameters.Add("FreightConfigurationExtentionId", freightConfigurationExtentionEntity.FreightConfigurationExtentionId, DbType.Int32, size: 10);
            //parameters.Add("ProvinceId", freightConfigurationExtentionEntity.ProvinceId, DbType.AnsiString, size: 20);
            //parameters.Add("CommunityId", freightConfigurationExtentionEntity.CommunityId, DbType.AnsiString, size: 20);
            //parameters.Add("CountyId", freightConfigurationExtentionEntity.CountyId, DbType.AnsiString, size: 20);
            //parameters.Add("StartWeight", freightConfigurationExtentionEntity.StartWeight, DbType.Decimal, size: 3);
            //parameters.Add("EndWeight", freightConfigurationExtentionEntity.EndWeight, DbType.Decimal, size: 3);
            //parameters.Add("Stere", freightConfigurationExtentionEntity.Stere, DbType.Int32, size: 10);
            //parameters.Add("StartPrice", freightConfigurationExtentionEntity.StartPrice, DbType.Decimal, size: 10);
            //parameters.Add("MileagePrice", freightConfigurationExtentionEntity.MileagePrice, DbType.Decimal, size: 10);
            //parameters.Add("Editer", freightConfigurationExtentionEntity.Editer, DbType.AnsiString, size: 20);
            //return await this.sqlCommand.ExecuteAsync(sql, parameters);
            return await database.Master.UpdateAsync(entity);
        }

        public async Task<FreightConfigurationExtentionExpandEntity> GetById(int freightConfigurationExtentionId)
        {
            var sql = $"{QUERY} WHERE FreightConfigurationExtentionId=?FreightConfigurationExtentionId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("FreightConfigurationExtentionId", freightConfigurationExtentionId, DbType.Int32, size: 10);
            return await database.Slave.QueryEntityAsync<FreightConfigurationExtentionExpandEntity>(sql, parameters);
        }

        public async Task<FreightConfigurationExtentionEntity> Get(string countyCode, decimal weight, OrderSourceType orderSourceType, FreightType? freightType)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(countyCode))
            {
                wheres.Add("CountyCode=?CountyCode");
                parameters.Add("?CountyCode", countyCode, DbType.AnsiString);
            }
            wheres.Add("MaxWeight>?Weight");
            parameters.Add("?Weight", weight, DbType.Decimal);
            wheres.Add("SourceType=?SourceType");
            parameters.Add("?SourceType", orderSourceType.ToString(), DbType.AnsiString);

            if (freightType.HasValue)
            {
                wheres.Add("FreightType=?FreightType");
                parameters.Add("?FreightType", freightType.ToString(), DbType.AnsiString);
            }
            var sql = string.Format("{0} {1} ORDER BY MaxWeight ASC LIMIT 1", QUERY_DEFAULT, (wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : ""));
            return await database.Slave.QueryFirstOrDefaultAsync<FreightConfigurationExtentionEntity>(sql, parameters);

        }

        public async Task<(IEnumerable<FreightConfigurationExtentionExpandEntity>, int)> GetAll(long? agentRecordId, FreightConfigurationExtentionViewEntity viewEntity)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (agentRecordId.HasValue)
            {
                wheres.Add("F.AgentRecordId = ?AgentRecordId");
                parameters.Add("?AgentRecordId", agentRecordId.Value, DbType.Int64);
            }
            if (viewEntity.VehicleTypeId.HasValue)
            {
                wheres.Add(" A.VehicleTypeId=?VehicleTypeId");
                parameters.Add("?VehicleTypeId", viewEntity.VehicleTypeId.Value, DbType.Int32);
            }
            if (!string.IsNullOrWhiteSpace(viewEntity.ProvinceCode))
            {
                wheres.Add("A.ProvinceCode=?ProvinceCode");
                parameters.Add("?ProvinceCode", viewEntity.ProvinceCode, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(viewEntity.CityCode))
            {
                wheres.Add("A.CityCode=?CityCode");
                parameters.Add("?CityCode", viewEntity.CityCode, DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(viewEntity.CountyCode))
            {
                wheres.Add("A.CountyCode=?CountyCode");
                parameters.Add("?CountyCode", viewEntity.CountyCode, DbType.AnsiString);
            }
            if (viewEntity.SourceType.HasValue)
            {
                wheres.Add("A.SourceType = ?SourceType");
                parameters.Add("?SourceType", viewEntity.SourceType.Value.ToString(), DbType.AnsiString);
            }
            if (viewEntity.FreightType.HasValue)
            {
                wheres.Add("A.FreightType = ?FreightType");
                parameters.Add("?FreightType", viewEntity.FreightType.Value.ToString(), DbType.AnsiString);
            }
            string sql = string.Format(@"{0} {1} LIMIT {2},{3};
                                         {4} {1}",
                agentRecordId.HasValue ? QUERY_JOIN : QUERY,
                wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                (viewEntity.PageIndex - 1) * viewEntity.PageSize,
                viewEntity.PageSize,
                agentRecordId.HasValue ? QUERY_JOIN_COUNT : QUERY_COUNT);
            return await database.Slave.QueryPageableListAsync<FreightConfigurationExtentionExpandEntity>(sql, parameters);
        }

        public async Task<IEnumerable<string>> GetCountyCodeList()
        {
            return await database.Slave.QueryAsync<string>("SELECT DISTINCT(CountyCode) FROM FreightConfigurationExtention");
        }

        public async Task<int> InsertReparir(string tempCountyCode, string countyCode)
        {
            return await database.Master.ExecuteAsync(@"INSERT INTO FreightConfigurationExtention(ProvinceCode,CityCode,CountyCode,
                            TransportMode,TransportModeStr,FirstWeight,MaxWeight,StartPrice,EveryWeightPrice,CreateTime,UpdateTime,Editer,SourceType,FreightType,
                            VehicleTypeId,MileagePrice)
                            SELECT B.ProvinceCode,B.CityCode,B.CountyCode,A.TransportMode,A.TransportModeStr,
                            A.FirstWeight,A.MaxWeight,A.StartPrice,A.EveryWeightPrice,NOW(),NOW(),'System',A.SourceType,A.FreightType,
                            A.VehicleTypeId,A.MileagePrice     
                            FROM FreightConfigurationExtention A,County B 
                            WHERE A.CountyCode = ?CountyCode AND B.CountyCode = ?TempCountyCode", new { countyCode, tempCountyCode });
        }

        public async Task<FreightConfigurationExtentionExpandEntity> GetFreight(int? vehicleTypeId, string provinceCode,
            string cityCode, string countyCode, OrderSourceType? sourceType, FreightType? freightType)
        {
            var wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (vehicleTypeId.HasValue)
            {
                wheres.Add(" A.VehicleTypeId=?VehicleTypeId");
                parameters.Add("?VehicleTypeId", vehicleTypeId);
            }
            if (!string.IsNullOrWhiteSpace(provinceCode))
            {
                wheres.Add("A.ProvinceCode=?ProvinceCode");
                parameters.Add("?ProvinceCode", provinceCode);
            }
            if (!string.IsNullOrWhiteSpace(cityCode))
            {
                wheres.Add("A.CityCode=?CityCode");
                parameters.Add("?CityCode", cityCode);
            }
            if (!string.IsNullOrWhiteSpace(countyCode))
            {
                wheres.Add("A.CountyCode=?CountyCode");
                parameters.Add("?CountyCode", countyCode);
            }
            if (sourceType.HasValue)
            {
                wheres.Add("A.SourceType = ?SourceType");
                parameters.Add("?SourceType", sourceType.Value.ToString(), DbType.AnsiString);
            }
            if (freightType.HasValue)
            {
                wheres.Add("A.FreightType = ?FreightType");
                parameters.Add("?FreightType", freightType.Value.ToString(), DbType.AnsiString);
            }
            var sql = string.Format("{0} {1} LIMIT 1", QUERY, (wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : ""));
            //return databaseContext.Master.QueryFirstOrDefaultAsync<FreightConfigurationEntity>(sql, parameters);
            return await database.Slave.QueryFirstOrDefaultAsync<FreightConfigurationExtentionExpandEntity>(sql, parameters);
        }

        public async Task<IEnumerable<int>> GetVehicleTypeIdList()
        {
            return await database.Slave.QueryAsync<int>("SELECT DISTINCT(VehicleTypeId) FROM FreightConfigurationExtention;");
        }

        public async Task<int> InsertReparir(int vehicleTypeId, int tempVehicleTypeId)
        {
            return await database.Master.ExecuteAsync(@"INSERT INTO FreightConfigurationExtention(ProvinceCode,CityCode,CountyCode,
                            FirstWeight,MaxWeight,StartPrice,EveryWeightPrice,CreateTime,UpdateTime,Editer,SourceType,FreightType,
                            VehicleTypeId,MileagePrice)
                            SELECT A.ProvinceCode,A.CityCode,A.CountyCode,
                            A.FirstWeight,A.MaxWeight,A.StartPrice,A.EveryWeightPrice,NOW(),NOW(),'System',A.SourceType,A.FreightType,
                            B.VehicleTypeId,A.MileagePrice    
                            FROM FreightConfigurationExtention A,
                            VehicleCategoryReference B 
                            WHERE A.VehicleTypeId = ?VehicleTypeId AND B.VehicleTypeId = ?TempVehicleTypeId;",
                            new
                            {
                                VehicleTypeId = vehicleTypeId,
                                TempVehicleTypeId = vehicleTypeId
                            });
        }

        #endregion 公共方法
    }
}
